package chen.web.forum.tag;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;

import chen.web.DataSourceProvider;
import chen.web.forum.Subject;
import chen.web.forum.TopicInfo;
import chen.web.forum.TopicManagerImpl;

public class TagManagerImpl implements TagManager {

	private SimpleJdbcTemplate simpleJdbcTemplate = new SimpleJdbcTemplate(DataSourceProvider.getDataSource());

	private static final String SQL_1 = "insert into web_tag(tag_name, tag_created, tag_flag) value(?,?,?)";
	@Override
	public void addTag(Tag tag) {
		// TODO Auto-generated method stub
		String name = tag.getName();
		if(name == null || name.length() == 0){
			throw new IllegalArgumentException("标签名不能为空");
		}
		simpleJdbcTemplate.update(SQL_1, name, new Date(), tag.getFlag());
	}

	private static final String SQL_2 = "delete from web_tag where tag_id = ?";
	@Override
	public void deleteTag(Tag tag) {
		// TODO Auto-generated method stub
		int id = tag.getId();
		simpleJdbcTemplate.update(SQL_2, id);
	}

	@Override
	public List<Tag> findTags(String text) {
		// TODO Auto-generated method stub
		return null;
	}

	private static final String SQL_7 = "select * from web_tag where tag_id = ?";
	@Override
	public Tag getTag(int id) {
		// TODO Auto-generated method stub
		Tag tag = null;
		try{
			tag = simpleJdbcTemplate.queryForObject(SQL_7, new TagRowMapper(), id);
		} catch(EmptyResultDataAccessException e){
			//do nothing
		}
		return tag;
	}
	
	private static final String SQL_4 = "update web_tag set tag_name = ?, tag_flag = ? where tag_id = ?";
	@Override
	public void modifyTag(Tag tag) {
		// TODO Auto-generated method stub
		String name = tag.getName();
		if(name == null || name.length() == 0){
			throw new IllegalArgumentException("标签名不能为空");
		} 
		simpleJdbcTemplate.update(SQL_4, name, tag.getFlag(), tag.getId());
	}
	
	private static class TagRowMapper implements RowMapper<Tag>{

		@Override
		public Tag mapRow(ResultSet rs, int rowNum) throws SQLException {
			// TODO Auto-generated method stub
			int id = rs.getInt("tag_id");
			String name = rs.getString("tag_name");
			int flag = rs.getInt("tag_flag");
			int subjectCount = rs.getInt("tag_subjectCount");
			Date created = rs.getTimestamp("tag_created");
			Tag tag = new Tag();
			tag.setName(name);
			tag.setId(id);
			tag.setFlag(flag);
			tag.setSubjectCount(subjectCount);
			tag.setCreated(created);
			return tag;
		}
		
	}
	
	private static final String SQL_3 = "select * from web_subject right join (select sub_id from web_subject_tag where tag_id = ?)t on web_subject.sub_id = t.sub_id order by sub_id desc limit ?,?";
	@Override
	public List<TopicInfo> getTopicInfoList(Tag tag, int beginOffset, int count) {
		// TODO Auto-generated method stub
		List<TopicInfo> list = simpleJdbcTemplate.query(SQL_3, new TopicManagerImpl.TopicInfoRowMapper(), tag.getId(), beginOffset, count);
		return list;
	}

	private static final String SQL_8 = "delete from web_subject_tag where tag_id=? and subject_id=?";
	@Override
	public void removeTag(Subject subject, Tag... tags) {
		// TODO Auto-generated method stub
		for(Tag tag : tags){
			simpleJdbcTemplate.update(SQL_8, subject.getId(), tag.getId());	
		}
	}

	private static final String SQL_5 = "select count(*) from web_subject_tag where tag_id = ?";
	@Override
	public int subjectAmount(Tag tag) {
		// TODO Auto-generated method stub
		int count = simpleJdbcTemplate.queryForInt(SQL_5);
		return count;
	}

	private static final String SQL_6 = "insert into web_subject_tag values(?,?)";
	@Override
	public void tagSubject(Subject subject, Tag... tags) {
		// TODO Auto-generated method stub
		for(Tag tag : tags){
			simpleJdbcTemplate.update(SQL_6, subject.getId(), tag.getId());	
		}
	}

	private static final String SQL_10 = "select tag_id , count(*) as tagCount from web_subject_tag right join (select sub_id from web_subject_tag where tag_id = ?)t on web_subject_tag.sub_id = t.sub_id  group by tag_id order by tagCount desc limit ?";
	@Override
	public List<Tag> getRelatedTags(Tag tag, int count) {
		// TODO Auto-generated method stub
		List<Integer> tagIds = simpleJdbcTemplate.query(SQL_10, new RowMapper<Integer>(){
			@Override
			public Integer mapRow(ResultSet rs, int arg1) throws SQLException {
				// TODO Auto-generated method stub
				return rs.getInt("tag_id");
			}
		}, tag.getId(), count);
		
		if(tagIds.size() == 0)	return new ArrayList<Tag>(0);
		
		StringBuilder sb = new StringBuilder();
		sb.append("select * from web_tag where tag_id in(");
		for(int i=0; i<tagIds.size(); i++){
			sb.append(tagIds.get(i));
			if(i+1 != tagIds.size()){
				sb.append(",");
			}
		}
		sb.append(")");
		List<Tag> tagList = simpleJdbcTemplate.query(sb.toString(), new TagRowMapper());
		for(int i=0,c=0; i<tagIds.size(); i++){
			for(int j=c; j<tagList.size(); j++){
				if(tagIds.get(i) == tagList.get(j).getId()){
					Tag t = tagList.get(c);
					tagList.set(c, tagList.get(j));
					tagList.set(j, t);
					++c;
					break;
				}
			}
		}
		return tagList;
	}

	private static final String SQL_9 = "select * from web_tag where tag_name = ?";
	@Override
	public Tag getTag(String tagName) {
		// TODO Auto-generated method stub
		Tag tag = null;
		try{
			tag = simpleJdbcTemplate.queryForObject(SQL_9, new TagRowMapper(), tagName);
		} catch(EmptyResultDataAccessException e){
			//do nothing
		}
		return tag;
	}

	private static final String SQL_13 = "select * from web_tag order by tag_subjectCount desc limit ?,?";
	@Override
	public List<Tag> getHotTags(int beginOffset, int count) {
		// TODO Auto-generated method stub
		List<Tag> list = simpleJdbcTemplate.query(SQL_13, new TagRowMapper(), beginOffset, count);
		return list;
	}

	private static final String SQL_14 = "select * from web_tag order by tag_id desc limit ?,?";
	@Override
	public List<Tag> getLatestTags(int beginOffset, int count) {
		// TODO Auto-generated method stub
		List<Tag> list = simpleJdbcTemplate.query(SQL_14, new TagRowMapper(), beginOffset, count);
		return list;
	}

	private static final String SQL_15 = "select count(*) from web_tag";
	@Override
	public int totalTag() {
		// TODO Auto-generated method stub
		int total = simpleJdbcTemplate.queryForInt(SQL_15);
		return total;
	}

	
}
